USE [Exp]
GO
/****** Object:  View [dbo].[vw_BLSummary]    Script Date: 11/18/2020 16:22:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[vw_BLSummary]
AS
SELECT DISTINCT 
                      dbo.Invoice_Bank.Inv_BankID, dbo.Invoice_Bank.Inv_BankVNo, dbo.Invoice_Bank.BLNo, dbo.Invoice_Bank.BLDate, dbo.Invoice_Bank.GD, dbo.Invoice_Bank.BankID, 
                      dbo.Invoice_Bank.ConsigneeID, dbo.Accounts.AccountTitle AS ConsigneeName, dbo.Invoice_Bank.ContainerTypeID, ISNULL(SSPInvoice.BENo, 0) AS SSPBL, 
                      ISNULL(AgentInvoice.BENo, 0) AS AgentBL, ISNULL(TransInvoice.BENo, 0) AS TransBL, ISNULL(MiscExpInvoice.BLNo, 0) AS MiscExpBL
FROM         dbo.Invoice_Bank LEFT OUTER JOIN
                      dbo.Accounts ON dbo.Invoice_Bank.ConsigneeID = dbo.Accounts.AccountNo LEFT OUTER JOIN
                          (SELECT DISTINCT BLNo
                            FROM          dbo.VW_Vouchers2 AS VW_Vouchers2_4
                            WHERE      (VoucherNo LIKE 'SI-%' OR
                                                   VoucherNo LIKE 'SA-%' OR
                                                   VoucherNo LIKE 'IM-%') AND (ContainerNo IS NOT NULL) AND (BLNo IN
                                                       (SELECT     BLNo
                                                         FROM          dbo.Voucher AS Voucher_3
                                                         WHERE      (VoucherNo LIKE 'MI-%')))) AS MiscExpInvoice ON dbo.Invoice_Bank.BLNo = MiscExpInvoice.BLNo LEFT OUTER JOIN
                          (SELECT DISTINCT 
                                                   dbo.Invoice_Trans.BENo, dbo.Invoice_Trans.ContainerNo, dbo.Invoice_TransDetail.TransID, dbo.Invoice_TransDetail.NetFreight, 
                                                   dbo.Invoice_TransDetail.SaleDate
                            FROM          dbo.Invoice_Trans LEFT OUTER JOIN
                                                   dbo.Invoice_TransDetail ON dbo.Invoice_Trans.Inv_TransID = dbo.Invoice_TransDetail.inv_TransID) AS TransInvoice ON 
                      dbo.Invoice_Bank.BLNo = TransInvoice.BENo LEFT OUTER JOIN
                          (SELECT DISTINCT dbo.Invoice_Agent.BENo, dbo.Invoice_Agent.ContainerNo, dbo.Invoice_AgentDetail.AgentID, dbo.Invoice_AgentDetail.SaleDate
                            FROM          dbo.Invoice_Agent LEFT OUTER JOIN
                                                   dbo.Invoice_AgentDetail ON dbo.Invoice_Agent.Inv_AgentID = dbo.Invoice_AgentDetail.inv_AgentID) AS AgentInvoice ON 
                      dbo.Invoice_Bank.BLNo = AgentInvoice.BENo LEFT OUTER JOIN
                          (SELECT DISTINCT dbo.Invoice_SSP.BENo, dbo.Invoice_SSParty.ContainerNo, dbo.Invoice_SSParty.ItemID, dbo.Invoice_SSParty.VarietyID
                            FROM          dbo.Invoice_SSP INNER JOIN
                                                   dbo.Invoice_SSParty ON dbo.Invoice_SSP.Inv_SSPID = dbo.Invoice_SSParty.inv_SSPID
                            WHERE      (dbo.Invoice_SSParty.ContainerNo IS NOT NULL)) AS SSPInvoice ON dbo.Invoice_Bank.BLNo = SSPInvoice.BENo
WHERE     (0 = 0)

------------------------------------------------------------------